﻿
//Author: 张亚涛
//createtime:2015-12-24 23:22:11
//Description:

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NS.DAL.Systems;
using Model;
using DAL;
using System.Collections;

namespace DAL
{
    /// <summary>
    /// 该类提供了一系列操作 "Order" 表的方法;
    /// </summary>
    public class OrderBase
    {
        const string SQL_Select = "select [OrderID],[OpenID],[OrderNo],[TrueName],[Mobile],[CarID],[CarNumber],[CarType],[Address],[Lat],[Lng],[Interior],[Times],[Price],[CouponsID],[CouType],[CouponsMoney],[Cash],[Pay],[Memo],[AddDate],[PayType],[TradeNo],[PayNo],[PayDate],[State],[UserID],[UserName],[UserMobile],[StartTime],[EndTime],[RealTime],[Score],[Comment] from [Order] ";
        #region .对本表的维护.
        #region select data
        /// <summary>
        /// 返回Order表的所有数据  
        /// </summary>
        /// <param name="OpenID">微信ID</param>
        /// <param name="State">状态(wf:未付 yf:已付 wc:完成)</param>
        /// <returns></returns>        
        public static List<Order> GetAllOrder(string OpenID, string State)
        {
            List<Order> orderList = new List<Order>();
            SqlDataReader reader = null;

            string sql = SQL_Select + " where OpenID='" + OpenID + "' ";
            if (State == "wf")
            {
                sql += " and State=0 ";
            }
            else if (State == "yf")
            {
                sql += " and (State=1 or State=2 or State=4) ";
            }
            else if (State == "wc")
            {
                sql += " and (State=3 or State=9) ";
            }
            else if (State == "tk")
            {
                sql += " and (State=7 or State=8) ";
            }
            sql += "order by OrderID DESC";
            reader = SQLHelper.ExecuteDataReader(sql, null);
            while (reader.Read())
            {
                Order order = new Order(reader);
                orderList.Add(order);
            }
            reader.Close();
            return orderList;
        }
        /// <summary>
        /// 返回员工订单列表  
        /// </summary>
        /// <param name="State">状态</param>
        /// <param name="OpenID">微信ID</param>
        /// <returns></returns>        
        public static List<Order> GetEmpOrder(string State, string OpenID)
        {
            List<Order> orderList = new List<Order>();
            SqlDataReader reader = null;
            string sql = SQL_Select + " where State='" + State + "' ";
            sql += " and UserID = (select UserID from UserDetail where WeChat = '" + OpenID + "') ";
            if (State == "4")
            {
                sql += " order by PayDate";
            }
            else if (State == "2")
            {
                sql += " order by StartTime";
            }
            else if (State == "3")
            {
                sql += " order by EndTime DESC";
            }
            reader = SQLHelper.ExecuteDataReader(sql, null);
            while (reader.Read())
            {
                Order order = new Order(reader);
                orderList.Add(order);
            }
            reader.Close();
            return orderList;
        }
        /// <summary>
        /// 根据Order表的OrderID字段返回数据 
        /// </summary>
        /// <param name="orderID">OrderID</param>
        /// <returns></returns>       
        public static Order GetOrderByOrderID(long orderID)
        {
            Order order = null;
            SqlDataReader reader = null;

            string sql = SQL_Select + "where [OrderID] = @OrderID";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@OrderID",orderID)
            };
            reader = SQLHelper.ExecuteDataReader(sql, paras);
            if (reader.Read())
            {
                order = new Order(reader);
            }
            reader.Close();
            return order;
        }
        /// <summary>
        /// 根据Order表的TradeNo字段返回数据  
        /// </summary>
        /// <param name="TradeNo">本方交易单号</param>
        /// <returns></returns>        
        public static Order GetOrderByTradeNo(string TradeNo)
        {
            Order order = null;
            SqlDataReader reader = null;

            string sql = SQL_Select + "where [TradeNo] = @TradeNo";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@TradeNo",TradeNo)
            };
            reader = SQLHelper.ExecuteDataReader(sql, paras);
            if (reader.Read())
            {
                order = new Order(reader);
            }
            reader.Close();
            return order;
        }
        /// <summary>
        /// 获得订单状态
        /// </summary>
        /// <param name="orderID">OrderID</param>
        /// <returns></returns>       
        public static int GetOrderState(string orderID)
        {
            int state = -1;
            int resInt;
            string sql = "select [State] from [Order] where [OrderID] = @OrderID";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@OrderID",orderID)
            };
            state = int.TryParse(SQLHelper.ExecuteScalar(sql, paras), out resInt) ? resInt : -1;
            return state;
        }
        /// <summary>
        /// 判断订单是否已完成支付
        /// </summary>
        /// <param name="TradeNo">交易单号</param>
        /// <returns></returns>       
        public static bool OrderIsPay(string TradeNo)
        {
            bool res = false;
            string sql = "select [PayNo] from [Order] where [TradeNo] = @TradeNo";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@TradeNo",TradeNo)
            };
            string state = SQLHelper.ExecuteScalar(sql, paras);
            if (!string.IsNullOrEmpty(state))
            {
                res = true;
            }
            return res;
        }
        /// <summary>
        /// 获得排队人数和等待时间
        /// </summary>
        /// <param name="PayDate">支付时间</param>
        /// <returns>[排队人数,等待时间]</returns>       
        public static string[] GetWaitInfo(string PayDate)
        {
            string[] res = new string[2];
            SqlDataReader reader = null;
            string sql = "SELECT COUNT(OrderID) WintCount,ISNULL(SUM(Times),0) Times FROM [Order] WHERE State = 1 ";
            if (!string.IsNullOrEmpty(PayDate))
            {
                sql += " and PayDate < '" + PayDate + "'";
            }
            reader = SQLHelper.ExecuteDataReader(sql, null);
            if (reader.Read())
            {
                res[0] = reader[0].ToString();
                res[1] = reader[1].ToString();
            }
            reader.Close();
            return res;
        }
        /// <summary>
        /// 获得员工派单情况
        /// </summary>
        /// <returns></returns>       
        public static DataTable GetUserAllot()
        {
            string strSQL = "SELECT " +
                            "	u.UserID, " +
                            "	u.TrueName, " +
                            "	u.Mobile, " +
                            "	ISNULL(o.wait, 0) Wait, " +
                            "	ISNULL(o.work, 0) Work " +
                            "FROM UserDetail u " +
                            "LEFT JOIN (SELECT " +
                            "	UserID, " +
                            "	SUM(CASE " +
                            "		WHEN State = 4 THEN 1 " +
                            "		ELSE 0 " +
                            "	END) AS wait, " +
                            "	SUM(CASE " +
                            "		WHEN State = 2 THEN 1 " +
                            "		ELSE 0 " +
                            "	END) AS work " +
                            "FROM [Order] " +
                            "WHERE State = 2 " +
                            "OR State = 4 " +
                            "GROUP BY UserID) o " +
                            "	ON o.UserID = u.UserID " +
                            "WHERE u.IsTrader = 1 ";

            return SQLHelper.ExecuteDataSet(strSQL, null).Tables[0];
        }
        /// <summary>
        /// 获得已指派员工
        /// </summary>
        /// <param name="orderID">orderID</param>
        /// <returns></returns>       
        public static string GetUserName(string orderID)
        {
            string sql = "SELECT UserName FROM [Order] WHERE OrderID='" + orderID + "' ";

            return SQLHelper.ExecuteScalar(sql, null);
        }
        #endregion
        #region delete data
        /// <summary>
        /// 根据Order表的OrderID字段删除数据 
        /// </summary>
        /// <param name="orderID">orderID</param>
        /// <returns></returns>        
        public static int DeleteOrderByOrderID(long orderID)
        {
            string sql = "delete from [Order] where [OrderID] = @OrderID";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@OrderID",orderID)
            };
            return SQLHelper.ExecuteNonQuery(sql, paras);
        }
        #endregion
        #region update data
        /// <summary>
        /// prepare parameters 
        /// </summary>
        public static SqlParameter[] PrepareCommandParameters(Order order)
        {
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@OrderID",order.OrderID),
            new SqlParameter("@OpenID",order.OpenID),
            new SqlParameter("@OrderNo",order.OrderNo),
            new SqlParameter("@TrueName",order.TrueName),
            new SqlParameter("@Mobile",order.Mobile),
            new SqlParameter("@CarID",order.CarID),
            new SqlParameter("@CarNumber",order.CarNumber),
            new SqlParameter("@CarType",order.CarType),
            new SqlParameter("@Address",order.Address),
            new SqlParameter("@Lat",order.Lat),
            new SqlParameter("@Lng",order.Lng),
            new SqlParameter("@Interior",order.Interior),
            new SqlParameter("@Times",order.Times),
            new SqlParameter("@Price",order.Price),
            new SqlParameter("@CouponsID",order.CouponsID),
            new SqlParameter("@CouType",order.CouType),
            new SqlParameter("@CouponsMoney",order.CouponsMoney),
            new SqlParameter("@Cash",order.Cash),
            new SqlParameter("@Pay",order.Pay),
            new SqlParameter("@Memo",order.Memo),
            new SqlParameter("@AddDate",order.AddDate),
            new SqlParameter("@PayType",order.PayType),
            new SqlParameter("@TradeNo",order.TradeNo),
            new SqlParameter("@PayNo",order.PayNo),
            new SqlParameter("@PayDate",order.PayDate),
            new SqlParameter("@State",order.State),
            new SqlParameter("@UserID",order.UserID),
            new SqlParameter("@UserName",order.UserName),
            new SqlParameter("@UserMobile",order.UserMobile),
            new SqlParameter("@StartTime",order.StartTime),
            new SqlParameter("@EndTime",order.EndTime),
            new SqlParameter("@RealTime",order.RealTime),
            new SqlParameter("@Score",order.Score),
            new SqlParameter("@Comment",order.Comment)  
            };
            return paras;
        }
        ///<summary>
        ///事务更新数据
        ///</summary>
        ///<param name="al">SQL语句(ArrayList)</param>
        ///<returns>True/False</returns>
        public static bool TransUpdate(ArrayList al)
        {
            return SQLHelper.TransUpdate(al);
        }
        #endregion
        #region insert data
        /// <summary>
        /// 向Order表插入一条数据，返回自增列数值，插入不成功则返回-1
        /// </summary>
        /// <param name="order">Order</param>       
        /// <returns></returns>        
        public static int AddOrder(Order order)
        {
            string sql = "insert into [Order]([OpenID],[OrderNo],[TrueName],[Mobile],[CarID],[CarType],[CarNumber],[Address],[Lat],[Lng],[Interior],[Times],[Price],[CouponsID],[CouType],[CouponsMoney],[Cash],[Pay],[Memo],[AddDate],[PayDate],[State])values(@OpenID,@OrderNo,@TrueName,@Mobile,@CarID,@CarType,@CarNumber,@Address,@Lat,@Lng,@Interior,@Times,@Price,@CouponsID,@CouType,@CouponsMoney,@Cash,@Pay,@Memo,@AddDate,@PayDate,@State)    select @@identity";
            SqlParameter[] paras = PrepareCommandParameters(order);
            int id = -1;

            string _ID = SQLHelper.ExecuteScalar(sql, paras).ToString();
            int.TryParse(_ID, out id);

            return id;
        }
        #endregion
        #endregion

        #region .SQL语句汇总.
        /// <summary>
        /// 向Order表插入一条数据（SQL语句）
        /// </summary>
        /// <param name="o">Order</param>       
        /// <returns></returns>        
        public static string AddOrder_SQL(Order o)
        {
            string sql = "insert into [Order]([OpenID],[OrderNo],[TrueName],[Mobile],[CarID],[CarType],[CarNumber],[Address],[Lat],[Lng],[Interior],[Times],[Price],[CouponsID],[CouType],[CouponsMoney],[Cash],[Pay],[Memo],[AddDate],[State]) ";
            sql += "values('" + o.OpenID + "','" + o.OrderNo + "','" + o.TrueName + "','" + o.Mobile + "','" + o.CarID + "','" + o.CarType + "','" + o.CarNumber + "','" + o.Address + "','" + o.Lat + "','" + o.Lng + "','" + o.Interior + "','" + o.Times + "','" + o.Price + "','" + o.CouponsID + "','" + o.CouType + "','" + o.CouponsMoney + "','" + o.Cash + "','" + o.Pay + "','" + o.Memo + "','" + o.AddDate + "','" + o.State + "')";

            return sql;
        }
        /// <summary>
        /// 更新状态（SQL语句）
        /// </summary> 
        /// <param name="orderID">OrderID</param>
        /// <param name="isUse">是否使用</param>
        /// <returns></returns>       
        public static string UpdateState_SQL(long orderID, int state)
        {
            string sql = "update [Order] set [State] = '" + state + "' where [OrderID] = '" + orderID + "'";
            return sql;
        }
        /// <summary>
        /// 返回Order表的所有数据 （SQL语句）
        /// </summary>
        /// <param name="State">状态</param>
        /// <param name="Mobile">手机号</param>
        /// <param name="OrderNo">订单号</param>
        /// <returns></returns>        
        public static string GetAllOrder_SQL(string State, string Mobile, string OrderNo)
        {
            string strSQL = SQL_Select + " where 1=1 ";
            if (!string.IsNullOrEmpty(State))
            {
                strSQL += " and State = '" + State + "'";
            }
            if (!string.IsNullOrEmpty(Mobile))
            {
                strSQL += " and Mobile = '" + Mobile + "'";
            }
            if (!string.IsNullOrEmpty(OrderNo))
            {
                strSQL += " and OrderNo = '" + OrderNo + "'";
            }
            return strSQL;
        }
        #endregion
    }
}